package com.sisi.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class FileParseUtils {
	
	public static String NUM_FORMAT = "#.########";
	
	public static List<Map<String,String>> parseExcel(String filePath) 
		   throws InvalidFormatException, FileNotFoundException, IOException{
		File file = new File(filePath);
		ObjectUtils.requireNotNull(file);
		return parseExcel(file);
	}
	
	public static List<Map<String,String>> parseExcel(File file) 
		   throws InvalidFormatException, FileNotFoundException, IOException{
		ObjectUtils.requireNotNull(file);
		Workbook wb = WorkbookFactory.create(new FileInputStream(file));
		List<Map<String,String>> rowList = new ArrayList<Map<String,String>>();
		for(int s=0;s<wb.getNumberOfSheets();s++){
			Sheet sheet = wb.getSheetAt(s);
			if(null != sheet){
				rowList.addAll(parseSheet(sheet));
			}
		}
		return rowList;
	}
	
	/**
	 * read excel file that specify sheet number
	 * @param file
	 * @param sheetNum the number of sheet (the first sheet number is 0)
	 * @return if no data,the size of list is 0 (the map means the data of row,
	 * 		   the key is the cell sequence number that is started by 1,
	 * 		   the value of the value string of cell)
	 * @throws InvalidFormatException
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static List<Map<String,String>> parseExcel(File file,int sheetNum) 
		   throws InvalidFormatException, FileNotFoundException, IOException{
		ObjectUtils.requireNotNull(file);
		Workbook wb = WorkbookFactory.create(new FileInputStream(file));
		Sheet sheet = wb.getSheetAt(sheetNum);
		if(null != sheet){
			return parseSheet(sheet);
		}else{
			throw new RuntimeException("no sheet in the excel file");
		}
	}
	
	/**
	 * transfer sheet to the List of Map that is the data of excel's line
	 * @param sheet
	 * @return
	 */
	public static List<Map<String,String>> parseSheet(Sheet sheet){
		ObjectUtils.requireNotNull(sheet);
		
		List<Map<String,String>> rowList = new ArrayList<Map<String,String>>();
		for(int r=0;r<=sheet.getLastRowNum();r++){
			Row row = sheet.getRow(r);
			if(null != row){
				Map<String,String> rowData = new HashMap<String, String>();
				for(int c=0;c<row.getLastCellNum();c++){
					Cell cell = row.getCell(c);
					if(cell != null){
						String cellVal = getCellStr(cell);
						rowData.put(""+(c+1), cellVal);
					}	
				}
				rowList.add(rowData);
			}
		}
		
		return rowList;
	}
	
	/**
	 * transfer cell to the String (date string is like yyyy-MM-dd HH:mm:ss)
	 * @param cell
	 * @return if the cell is empty,the return value is the String that is ""
	 */
	public static String getCellStr(Cell cell){
		ObjectUtils.requireNotNull(cell);
		String cellValue = null;
		switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_STRING:  
            cellValue = cell.getRichStringCellValue().getString().trim();  
            break;  
        case Cell.CELL_TYPE_NUMERIC:
            cellValue = getNumericCellStr(cell);  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();  
            break;  
        case Cell.CELL_TYPE_FORMULA:  
            cellValue = cell.getCellFormula();  
            break;  
        default:  
            cellValue = cell.toString();  
        }  
        return cellValue;
	}
	
	/**
	 * get the CELL_TYPE_NUMERIC data including date and number
	 * @param cell
	 * @return
	 */
	public static String getNumericCellStr(Cell cell){
		ObjectUtils.requireNotNull(cell);
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			if(DateUtil.isCellDateFormatted(cell)){
				return getDateCellStr(cell);
			}else{
				return getNumCellStr(cell);
			}
		}else{
			throw new IllegalArgumentException("The Type of cell is not number");
		}
	}
	
	public static String getNumCellStr(Cell cell){
		ObjectUtils.requireNotNull(cell);
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			if(!DateUtil.isCellDateFormatted(cell)){
				DecimalFormat df = new DecimalFormat(NUM_FORMAT);
				return df.format(cell.getNumericCellValue()).toString();
			}else{
				throw new IllegalArgumentException("The Type of cell is not number");
			}
		}else{
			throw new IllegalArgumentException("The Type of cell is not number");
		}
	}
	
	public static String getDateCellStr(Cell cell){
		ObjectUtils.requireNotNull(cell);
		if(DateUtil.isCellDateFormatted(cell)){
			Date cellDate = cell.getDateCellValue();
			return DateUtils.dateFomat(cellDate, "yyyy-MM-dd HH:mm:ss");
		}else{
			throw new IllegalArgumentException("The Type of cell is not Date");
		}
	}
	
	public static void main(String[] args) throws Exception{
		List<Map<String, String>> parseExcel = FileParseUtils.parseExcel("E:/ProjectSource/DEP/document/FVP/dep节点.xlsx");
		for (Map<String, String> map : parseExcel) {
			System.out.println(map);
		}
	}
}
